package cz.acies.uniql.sql.functions;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.dom.DOMElement;


import cz.acies.uniql.dialect.*;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLColumn;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLCriterion;
import cz.acies.uniql.sql.element.UniQLValue;
import cz.acies.uniql.utils.UniQLConfig;
import cz.acies.utils.StringUtils;

/**
 * T��da SQLFunc zajist� transformaci funkce SQL DB do spr�vn�ho form�tu 
 * pro dan� dialekt 
 *  
 * <table border=1>
 * <tr><td colspan=2 bgcolor=FFCC00 align=center>Registrovan� funkce v dialektech:</td></tr>
 * 
 * <tr><td colspan=2 bgcolor=FFCC00 align=center>Agregate functions</td></tr>
 * 
 * <tr><td>COUNT </td><td>COUNT($1)</td></tr>
 * <tr><td>SUM </td><td>SUM($1)</td></tr>
 * <tr><td>AVG </td><td>AVG($1)</td></tr>
 * <tr><td>MAX </td><td>MAX($1)</td></tr>
 * <tr><td>MIN </td><td>MIN($1)</td></tr>

 * <tr><td colspan=2 bgcolor=FFCC00 align=center>&nbsp</td></tr>
 * 
 * <tr><td>SQL_CALC_FOUND_ROWS </td><td>SQL_CALC_FOUND_ROWS</td></tr>
 * <tr><td>ROWNUMBER </td><td>&nbsp</td></tr>
 * <tr><td>OVER </td><td>&nbsp</td></tr>
 * <tr><td>CONNECTION_ID </td><td>CONNECTION_ID()</td></tr>
 *		
 * <tr><td colspan=2 bgcolor=FFCC00 align=center>Convert functions</td></tr>
 *		
 * <tr><td>CONVERT </td><td>$1</td></tr>
 * <tr><td>TEXT_TO_VARCHAR </td><td>$1</td></tr>
 * <tr><td>TO_VARCHAR </td><td>$1</td></tr>
 * <tr><td>TO_NUMBER </td><td>$1</td></tr>
 * <tr><td>TO_DATE </td><td>$1</td></tr>
 *		
 * <tr><td colspan=2 bgcolor=FFCC00 align=center> String functions </td></tr>
 *
 * <tr><td>LEFT </td><td>LEFT($1, $2)</td></tr>
 * <tr><td>RIGHT </td><td>RIGHT($1, $2)</td></tr>
 * <tr><td>SUBSTRING </td><td>SUBSTRING($1, $2, $3)</td></tr>
 * <tr><td>LTRIM </td><td>LTRIM($1)</td></tr>
 * <tr><td>RTRIM </td><td>RTRIM($1)</td></tr>
 * <tr><td>TRIM </td><td>LTRIM(RTRIM($1))</td></tr>
 * <tr><td>LPAD </td><td>LPAD($1, $2, $3)</td></tr>
 * <tr><td>RPAD </td><td>RPAD($1, $2, $3)</td></tr>
 * <tr><td>LENGTH </td><td>LENGTH($1)</td></tr>
 * <tr><td>CONCAT </td><td>CONCAT($n{, })</td></tr>
 * <tr><td>PASSWORD </td><td>$1</td></tr>
 * <tr><td>LOCATE </td><td>LOCATE($1, $2, $3)</td></tr>
 * <tr><td>REPLACE </td><td>REPLACE($1, $2, $3)</td></tr>
 *		
 * <tr><td colspan=2 bgcolor=FFCC00 align=center> Date & time functions </td></tr>
 *
 * <tr><td>NOW </td><td>NOW()</td></tr>
 * <tr><td>DATEADD </td><td>MINUTE + INTERVAL ($2) $1</td></tr>
 *
 * <tr><td colspan=2 bgcolor=FFCC00 align=center> Logic functions </td></tr>
 *
 * <tr><td>IF </td><td>IF($1, $2, $3)</td></tr>
 * <tr><td>ISNULL </td><td>ISNULL($1)</td></tr>
 * <tr><td>IF_ISNULL </td><td>IF(ISNULL($1),$2,$3)</tr></td>
 * </table>
 * <br>
 * Funkce jsou uv�d�ny s ekvivalenty z MySQL
 * 
 */
public class SQLFunc extends UniQLExpr
{
	private static final long serialVersionUID = 4822643775683612923L;
	
	private String[] pattern;
	private ArrayList<Object> parameters;
	
	public final static Set<String> agregFnSet = new HashSet<String>(); {
		agregFnSet.add("sum");
		agregFnSet.add("avg");
		agregFnSet.add("max");
		agregFnSet.add("min");
	}

	public SQLFunc() {
	}

	public SQLFunc(Dialect dialect) {
		super(dialect);
	}

	public SQLFunc(String name) {
		super();
		this.setName(name.toLowerCase());		
	}

	/**
	 * Nastav� priv�tn� prom�nnou name.
	 * 
	 * @param dialect prom�nn� typu dialect
	 */
	public void setName(String name) {
		if (UniQLConfig.DEBUG_MODE && !(this instanceof SQLAgregFunc)) {
			if (agregFnSet.contains(name)) {				
				new Exception("Function "+name.toUpperCase()+" must by created as SQLAgregFunc. ").printStackTrace(); 
			}
		}
		super.setName(name);
	}

	// -----  -----------------------------------------------------------------

	public SQLFunc addParam(String column) {
		if (this.parameters == null) this.parameters = new ArrayList<Object>();
		UniQLColumn expr = new UniQLColumn(column);
		return addParam(expr);
	}
	
	public SQLFunc addParam(UniQLExpr expr) {
		if (this.parameters == null) this.parameters = new ArrayList<Object>();
		expr.setDialect(this.getDialect());
		this.parameters.add(expr);		
		return this;
	}

	/**
	 * Metoda p�id� pole parametr� typu Object. Polo�ky pole mus� b�t potomkem 
	 * String nebo UniQLExpr.
	 * P��klad pou�it�"
	 * 
	 * 	SQLFunc concatFn = new SQLFunc("CONCAT")
	 * 		.addParams(new Object[] { "subjecttree_folder.PATH", new UniQLValue("'/'") });
	 * SQLFunc locateFn = new SQLFunc("LOCATE")
	 * 		.addParams(new Object[] { new UniQLValue("'/'"), concatFn, new UniQLValue("2") }
	 *  );
	 * SQLFunc leftFn = new SQLFunc("LEFT").addParams(new Object[] {concatFn, locateFn});
	 * 
	 * @param params
	 * @return
	 */
	public SQLFunc addParams(Object[] params) {
		List<Object> list = Arrays.asList(params); 
		return addParams(list);
	}
	
	/**
	 * Metoda p�id� list parametr� typu Object. Vpodstat� stejn� 
	 * jako addParams(Object[] params).
	 *  
	 * @param params
	 * @return
	 */
	public SQLFunc addParams(List<Object> params) {
		for (Iterator<Object> iterator = params.iterator(); iterator.hasNext();) {
			Object obj = iterator.next();
			if (obj instanceof String) {
				addParam(new UniQLColumn((String)obj));				
			} else if (obj instanceof UniQLExpr) {
				addParam((UniQLExpr)obj);
			}
		}
		return this;
	}
	
	//private static String FN_ABSTRAC_OPTIONAL_PATTERN = "\\[\\s?+(\\,?)\\$\\d\\s?+\\]";
	private static String FN_ABSTRAC_OPTIONAL_PATTERN = "\\[\\s?+\\,?\\s?+\\$\\d\\s?+\\]";
	
	public String translate() throws Exception {
		Dialect dialect = this.getDialect();
		//System.out.println("name = " + this.getName());
		//System.out.println("dialect = " + dialect);
		//System.out.println("dialect.getFnReg = " + dialect.getFnReg());
		String syntax = dialect.getFunctionSyntax(this.getName());
		String s = "";
		if (parameters != null) {
			int size = parameters.size();
			for (int i = 0; i < size; i++) {
				Object obj = parameters.get(i);
				String pattern = "$"+(i+1);

				if (obj instanceof UniQLExpr) {
					UniQLExpr expr = (UniQLExpr)obj;
					expr.setUniQLProperties(this);
					//expr.setDialect(dialect);
					s = expr.translate();
				} else if (obj instanceof String) {
					s = obj.toString();
				} else {
					s = "";
				}
				String FN_CURRECT_OPTIONAL_PATTERN = "(\\[\\s?+(\\,?)\\s?+\\$"+(i+1)+"\\])";
				Pattern p = Pattern. compile(FN_CURRECT_OPTIONAL_PATTERN);
				Matcher matcher = p.matcher(syntax);
				boolean exist = matcher.find(); 
				if (exist) {
					int cnt = matcher.groupCount();
					if (cnt > 1) s = matcher.group(2) + s;
					String repl = FN_CURRECT_OPTIONAL_PATTERN; 
					syntax = syntax.replaceAll(repl, s);
				} else {
					syntax = StringUtils.replace(syntax, pattern, s);
				}
			}
			syntax = syntax.replaceAll(FN_ABSTRAC_OPTIONAL_PATTERN, "");
			int pos = syntax.indexOf("$n");
			if (pos > -1) {
				int pos1 = syntax.indexOf("{", pos);
				int pos2 = syntax.indexOf("}", pos);
				String delim = "";
				if (pos1 > -1 && pos2 > pos1) {
					delim = syntax.substring(pos1+1, pos2);
					String shl = syntax.substring(0,pos1);
					String shr = syntax.substring(pos2+1);
					syntax = shl + shr;
				}
				String pattern = "$n";
				s = "";
				for (int i = 0; i < size; i++) {
					Object obj = parameters.get(i);
					if (obj instanceof UniQLExpr) {
						UniQLExpr expr = (UniQLExpr)obj;
						expr.setDialect(dialect);
						s += expr.translate();
					} else if (obj instanceof String) {
						s += obj.toString();
					}
					if (i < (size-1)) s += delim; 
				}
				syntax = StringUtils.replace(syntax, pattern, s);
			}
		}
		return syntax;
	}

	public String[] patternParse(String syntax) {
		int p1 = syntax.indexOf("(");
		int p2 = syntax.indexOf(")");
		if (p1 > -1 && p2 > p1 ) {
			String sxp = syntax.substring(p1+1, p2);
			//System.out.println("sxp = "+sxp);
			StringTokenizer st = new StringTokenizer(sxp, ",");
			int count = st.countTokens();
			pattern = new String[count];
			int i = 0;
			while (st.hasMoreElements()) {
				String param = (String) st.nextElement();
				pattern[i] = param.trim(); 
				i++;
			}
		}		
		return this.pattern; 
	}
	
	// -----  -----------------------------------------------------------------
	
	/**
	 * Metoda vrac� list parametr� funkce ulo�en�ch v priv�tn� prom�nn� parameters.  
	 */
	public ArrayList<Object> getParameters() {
		return parameters;
	}

	/**
	 *  Metoda nahrad� st�valic� list parametr� nov�m.
	 * @param parameters
	 */
	public void setParameters(ArrayList<Object> parameters) {
		this.parameters = parameters;
	}

	public String[] getPattern() {
		return pattern;
	}

	public void setPattern(String[] pattern) {
		this.pattern = pattern;
	}

	// -----  -----------------------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#transToXML()
	 */
	public String transToXML() throws Exception {
		DOMElement node = new DOMElement("SQLFunc");
		transToXML(node);
		return node.asXML();
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#transToXML(org.dom4j.dom.DOMElement)
	 */
	public DOMElement transToXML(DOMElement node) throws Exception {
		//String columnName = this.getName();
		node.setName("SQLFunc");
		node.setAttribute("name", this.getName());
		if (parameters != null) {
			int size = parameters.size();
			for (int i = 0; i < size; i++) {
				Object obj = parameters.get(i);
				if (obj instanceof UniQLExpr) {
					UniQLExpr expr = (UniQLExpr)obj;
					DOMElement param = expr.transToXML(new DOMElement("UniQLExpr"));
					if (param != null) {
						node.appendChild(param);	
					}
				}
			}			
		}		
		return node;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#xmlToExpr(java.lang.String)
	 */
	public UniQLExpr xmlToExpr(String xml) throws Exception {
		Document doc = DocumentHelper.parseText(xml);
		return xmlToExpr(doc.getRootElement());
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#xmlToExpr(org.dom4j.Element)
	 */
	@Override
	@SuppressWarnings("unchecked")
	public UniQLExpr xmlToExpr(Element node) throws Exception {
		ArrayList<Element> params = new ArrayList<Element>(node.elements());
		String name = node.attributeValue("name");
		this.setName(name);
		for (int i = 0; i < params.size(); i++) {
			Element param = (Element)params.get(i);
			String nodename = param.getName();
			//System.out.println("SQLFunc.xmlToExpr().nodename = "+nodename);
			UniQLExpr expr = null; 
			if (nodename.trim().equals("UniQLColumn")) {
				expr = new UniQLColumn(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("UniQLValue")) {
				expr = new UniQLValue(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("UniQLCriterion")) {
				expr = new UniQLCriterion(getDialect()).xmlToExpr(param);	
			} else if (nodename.trim().equals("UniQLCondition")) {
				expr = new UniQLCondition(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("SQLAritFunc")) {
				expr = new SQLAritFunc(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("SQLAritFunc2")) {
				expr = new SQLAritFunc2(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("SQLAgregFunc")) {
				expr = new SQLFunc(getDialect()).xmlToExpr(param);
			} else if (nodename.trim().equals("SQLFunc")) {	
				expr = new SQLAgregFunc(getDialect()).xmlToExpr(param);
			}
			if (expr != null) {
				this.addParam(expr);
			}
		}		
		return this;
	}

	//---------------------------------

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	public String toString() {
		return ""+this.getName()+" ("+this.getParameters() +")";
	}
}
